LISTAGG Function in Oracle DataBase

Suppose we have this query and the result.

This query retrieves all the first names of employees who are working in department id 30. But what if we want to see first_names in one single line according to the department id?

In this situation, we can just simply use LISTAGG function. it returns one value after calculating multiple values at a column.

Let’s take a look at the syntax of this function.

Let’s aggregate first names who are in department id 30.

I put first_name as an argument for the measure_expression parameter, and set the delimiter as ‘, ’. This query retrieves a consolidated list of first names for individuals working in department with id 30. The outcome is a single line and seperated by commas and spaces.

If we want to retrieve other columns , we have to use group by clause at the end of query and specify the columns appeared on the select statement. Because LISTAGG function is an aggregate function.

Above tuple shows distinct data of employee’s first names. However, In real world, we will face many duplicate data.

Suppose we need a list of jobs in each department_id

If we look at the department_id 30 , It has total 6 employees and 5 employees working as an PU_CLERK which is duplicate.

Let’s enhance the query by eliminating the duplicates. To do that, we have to pick the distinct job_id in each department_id.

Let’s use subquery approach to select distinct combinations of DEPARTMENT_ID and JOB_ID and aggregate the result tuples with LISTAGG function.

By using group by clause in subquery, We consolidated the duplicate data into one. So that LISTAGG function can get the distinct data of job_id.

Analytical syntax refers to using the function as an analytic or window function. In SQL, analytic functions perform a calculation across a specified range of rows related to the current row, often defined by an OVER clause.

Below is the LISTAGG function as an analytical syntax.

Let’s take an example to make the picture more clear.

The query above retrieves department_id and lists of job_ids separated by comma and space within the range of department_id from employees table.

It shows the data within the range of each department id. You might notice that department_id 30 has multiple duplicate list of jobs. It is because partitioning is done based on the department_id.

The result means that there is 6 jobs in the department 30. It means that department 30 have 6 distinct rows in the first place.

Let’s see what it happens if we add more columns.

Now we can clearly see that department id 30 has 6 distinct rows. By using LISTAGG function we just added aggregate function based on each distinct department_id rows.

LISTAGG is a powerful SQL function designed for consolidating and organizing data within a specified context. By using LISTAGG function , It enhances the readability of the list data.